/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

/**
 *
 * @author spthanh
 */
public class Batches_CallableStmt_Example {
    public static void main(String[] args){
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks", "sa", "123456");

            //Turn off auto-commit
            con.setAutoCommit(false);

            //Create instance of callableStatement
            CallableStatement cstmt = con.prepareCall("{call proc_UpdateStudent(?,?,?,?,?,?)}");

            cstmt.setInt("id", 4);

            //Set value for parameters to insert
            cstmt.setString(2, "Thanh");
            cstmt.setString(3, "Alex");
            cstmt.setString(4, "22 Ly thai To");
            cstmt.setInt(5, 29);
            cstmt.setString(6, "Description");

            //Add batches into CallableStatement
            cstmt.addBatch();


            cstmt.setInt("id", 3);

            //Set value for parameters to insert
            cstmt.setString(2, "Thanh");
            cstmt.setString(3, "Alex");
            cstmt.setString(4, "22 Ly thai To");
            cstmt.setInt(5, 29);
            cstmt.setString(6, "Description");

            cstmt.addBatch();

            //Execute the batches for preparedStatement
            int[] results =  cstmt.executeBatch();
            for(int item : results){
                System.out.println("Value return is:" + item);
            }

           //Commit changes into the database
           con.commit();

           //Clear the batches into callableStatement
           cstmt.clearBatch();

           //Set enable for auto-commit
           con.setAutoCommit(true);


        }
        catch(Exception e){
            e.printStackTrace();
        }
    }
}
